<!DOCTYPE html>
<html lang="en">
<head>
	<meta charset="UTF-8">
	<meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1">
	<title>Lexical Structure | ElasticSearch 7.7 权威指南中文版</title>
	<meta name="keywords" content="ElasticSearch 权威指南中文版, elasticsearch 7, es7, 实时数据分析，实时数据检索" />
    <meta name="description" content="ElasticSearch 权威指南中文版, elasticsearch 7, es7, 实时数据分析，实时数据检索" />
    <!-- Give IE8 a fighting chance -->
    <!--[if lt IE 9]>
    <script src="https://oss.maxcdn.com/html5shiv/3.7.2/html5shiv.min.js"></script>
    <script src="https://oss.maxcdn.com/respond/1.4.2/respond.min.js"></script>
    <![endif]-->
	<link rel="stylesheet" type="text/css" href="../static/styles.css" />
	<script>
	var _link = 'sql-lexical-structure.html';
    </script>
</head>
<body>
<div class="main-container">
    <section id="content">
        <div class="content-wrapper">
            <section id="guide" lang="zh_cn">
                <div class="container">
                    <div class="row">
                        <div class="col-xs-12 col-sm-8 col-md-8 guide-section">
                            <div style="color:gray; word-break: break-all; font-size:12px;">原英文版地址: <a href="https://www.elastic.co/guide/en/elasticsearch/reference/7.7/sql-lexical-structure.html" rel="nofollow" target="_blank">https://www.elastic.co/guide/en/elasticsearch/reference/7.7/sql-lexical-structure.html</a>, 原文档版权归 www.elastic.co 所有<br/>本地英文版地址: <a href="../en/sql-lexical-structure.html" rel="nofollow" target="_blank">../en/sql-lexical-structure.html</a></div>
                        <!-- start body -->
                  <div class="page_header">
<strong>重要</strong>: 此版本不会发布额外的bug修复或文档更新。最新信息请参考 <a href="https://www.elastic.co/guide/en/elasticsearch/reference/current/index.html" rel="nofollow">当前版本文档</a>。
</div>
<div id="content">
<div class="breadcrumbs">
<span class="breadcrumb-link"><a href="index.html">Elasticsearch Guide [7.7]</a></span>
»
<span class="breadcrumb-link"><a href="xpack-sql.html">SQL access</a></span>
»
<span class="breadcrumb-link"><a href="sql-spec.html">SQL Language</a></span>
»
<span class="breadcrumb-node">Lexical Structure</span>
</div>
<div class="navheader">
<span class="prev">
<a href="sql-spec.html">« SQL Language</a>
</span>
<span class="next">
<a href="sql-commands.html">SQL Commands »</a>
</span>
</div>
<div class="section xpack">
<div class="titlepage"><div><div>
<h2 class="title">
<a id="sql-lexical-structure"></a>Lexical Structure<a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/language/syntax/lexic/index.asciidoc">edit</a><a class="xpack_tag" href="https://www.elastic.co/subscriptions"></a>
</h2>
</div></div></div>
<p>This section covers the major lexical structure of SQL, which for the most part, is going to resemble that of ANSI SQL itself hence why low-levels details are not discussed in depth.</p>
<p>Elasticsearch SQL currently accepts only one <em>command</em> at a time. A command is a sequence of <em>tokens</em> terminated by the end of input stream.</p>
<p>A token can be a <em>key word</em>, an <em>identifier</em> (<em>quoted</em> or <em>unquoted</em>), a <em>literal</em> (or constant) or a special character symbol (typically a delimiter). Tokens are typically separated by whitespace (be it space, tab) though in some cases, where there is no ambiguity (typically due to a character symbol) this is not needed - however for readability purposes this should be avoided.</p>
<div class="section">
<div class="titlepage"><div><div>
<h3 class="title">
<a id="sql-syntax-keywords"></a>Key Words<a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/language/syntax/lexic/index.asciidoc">edit</a>
</h3>
</div></div></div>
<p>Take the following example:</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT * FROM table</pre>
</div>
<p>This query has four tokens: <code class="literal">SELECT</code>, <code class="literal">*</code>, <code class="literal">FROM</code> and <code class="literal">table</code>. The first three, namely <code class="literal">SELECT</code>, <code class="literal">*</code> and <code class="literal">FROM</code> are <em>key words</em> meaning words that have a fixed meaning in SQL. The token <code class="literal">table</code> is an <em>identifier</em> meaning it identifies (by name) an entity inside SQL such as a table (in this case), a column, etc…​</p>
<p>As one can see, both key words and identifiers have the <em>same</em> lexical structure and thus one cannot know whether a token is one or the other without knowing the SQL language; the complete list of key words is available in the <a class="xref" href="sql-syntax-reserved.html" title="Reserved keywords">reserved appendix</a>.
Do note that key words are case-insensitive meaning the previous example can be written as:</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">select * fRoM table;</pre>
</div>
<p>Identifiers however are not - as Elasticsearch is case sensitive, Elasticsearch SQL uses the received value verbatim.</p>
<p>To help differentiate between the two, through-out the documentation the SQL key words are upper-cased a convention we find increases readability and thus recommend to others.</p>
</div>

<div class="section">
<div class="titlepage"><div><div>
<h3 class="title">
<a id="sql-syntax-identifiers"></a>Identifiers<a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/language/syntax/lexic/index.asciidoc">edit</a>
</h3>
</div></div></div>
<p>Identifiers can be of two types: <em>quoted</em> and <em>unquoted</em>:</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT ip_address FROM "hosts-*"</pre>
</div>
<p>This query has two identifiers, <code class="literal">ip_address</code> and <code class="literal">hosts-*</code> (an <a class="xref" href="multi-index.html" title="Multiple indices">index pattern</a>). As <code class="literal">ip_address</code> does not clash with any key words it can be used verbatim, <code class="literal">hosts-*</code> on the other hand cannot as it clashes with <code class="literal">-</code> (minus operation) and <code class="literal">*</code> hence the double quotes.</p>
<p>Another example:</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT "from" FROM "&lt;logstash-{now/d}&gt;"</pre>
</div>
<p>The first identifier from needs to quoted as otherwise it clashes with the <code class="literal">FROM</code> key word (which is case insensitive as thus can be written as <code class="literal">from</code>) while the second identifier using Elasticsearch <a class="xref" href="date-math-index-names.html" title="Date math support in index names">Date math support in index names</a> would have otherwise confuse the parser.</p>
<p>Hence why in general, <span class="strong strong"><strong>especially</strong></span> when dealing with user input it is <span class="strong strong"><strong>highly</strong></span> recommended to use quotes for identifiers. It adds minimal increase to your queries and in return offers clarity and disambiguation.</p>
</div>

<div class="section">
<div class="titlepage"><div><div>
<h3 class="title">
<a id="sql-syntax-literals"></a>Literals (Constants)<a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/language/syntax/lexic/index.asciidoc">edit</a>
</h3>
</div></div></div>
<p>Elasticsearch SQL supports two kind of <em>implicitly-typed</em> literals: strings and numbers.</p>
<h5>
<a id="sql-syntax-string-literals"></a>String Literals<a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/language/syntax/lexic/index.asciidoc">edit</a>
</h5>
<p>A string literal is an arbitrary number of characters bounded by single quotes <code class="literal">'</code>: <code class="literal">'Giant Robot'</code>.
To include a single quote in the string, escape it using another single quote: <code class="literal">'Captain EO''s Voyage'</code>.</p>
<div class="note admon">
<div class="icon"></div>
<div class="admon_content">
<p>An escaped single quote is <span class="strong strong"><strong>not</strong></span> a double quote (<code class="literal">"</code>), but a single quote <code class="literal">'</code> <em>repeated</em> (<code class="literal">''</code>).</p>
</div>
</div>
<h5>
<a id="_numeric_literals"></a>Numeric Literals<a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/language/syntax/lexic/index.asciidoc">edit</a>
</h5>
<p>Numeric literals are accepted both in decimal and scientific notation with exponent marker (<code class="literal">e</code> or <code class="literal">E</code>), starting either with a digit or decimal point <code class="literal">.</code>:</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">1969    -- integer notation
3.14    -- decimal notation
.1234   -- decimal notation starting with decimal point
4E5     -- scientific notation (with exponent marker)
1.2e-3  -- scientific notation with decimal point</pre>
</div>
<p>Numeric literals that contain a decimal point are always interpreted as being of type <code class="literal">double</code>. Those without are considered <code class="literal">integer</code> if they fit otherwise their type is <code class="literal">long</code> (or <code class="literal">BIGINT</code> in ANSI SQL types).</p>
<h5>
<a id="sql-syntax-generic-literals"></a>Generic Literals<a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/language/syntax/lexic/index.asciidoc">edit</a>
</h5>
<p>When dealing with arbitrary type literal, one creates the object by casting, typically, the string representation to the desired type. This can be achieved through the dedicated <a class="xref" href="sql-operators-cast.html" title="Cast Operators">cast operator</a> and <a class="xref" href="sql-functions-type-conversion.html" title="Type Conversion Functions">functions</a>:</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">123::LONG                                   -- cast 123 to a LONG
CAST('1969-05-13T12:34:56' AS TIMESTAMP)    -- cast the given string to datetime
CONVERT('10.0.0.1', IP)                     -- cast '10.0.0.1' to an IP</pre>
</div>
<p>Do note that Elasticsearch SQL provides functions that out of the box return popular literals (like <code class="literal">E()</code>) or provide dedicated parsing for certain strings.</p>
</div>

<div class="section">
<div class="titlepage"><div><div>
<h3 class="title">
<a id="sql-syntax-single-vs-double-quotes"></a>Single vs Double Quotes<a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/language/syntax/lexic/index.asciidoc">edit</a>
</h3>
</div></div></div>
<p>It is worth pointing out that in SQL, single quotes <code class="literal">'</code> and double quotes <code class="literal">"</code> have different meaning and <span class="strong strong"><strong>cannot</strong></span> be used interchangeably.
Single quotes are used to declare a <a class="xref" href="sql-lexical-structure.html#sql-syntax-string-literals" title="String Literals">string literal</a> while double quotes for <a class="xref" href="sql-lexical-structure.html#sql-syntax-identifiers" title="Identifiers">identifiers</a>.</p>
<p>To wit:</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT "first_name" <a id="CO66-1"></a><i class="conum" data-value="1"></i>
  FROM "musicians"  <a id="CO66-2"></a><i class="conum" data-value="1"></i>
 WHERE "last_name"  <a id="CO66-3"></a><i class="conum" data-value="1"></i>
     = 'Carroll'    <a id="CO66-4"></a><i class="conum" data-value="2"></i></pre>
</div>
<div class="calloutlist">
<table border="0" summary="Callout list">
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO66-1"><i class="conum" data-value="1"></i></a><a href="#CO66-2"></a><a href="#CO66-3"></a></p>
</td>
<td align="left" valign="top">
<p>Double quotes <code class="literal">"</code> used for column and table identifiers</p>
</td>
</tr>
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO66-4"><i class="conum" data-value="2"></i></a></p>
</td>
<td align="left" valign="top">
<p>Single quotes <code class="literal">'</code> used for a string literal</p>
</td>
</tr>
</table>
</div>
<div class="note admon">
<div class="icon"></div>
<div class="admon_content">
<p>To escape single or double quotes, one needs to use that specific quote one more time. For example, the literal <code class="literal">John's</code> can be escaped like
<code class="literal">SELECT 'John''s' AS name</code>. The same goes for double quotes escaping - <code class="literal">SELECT 123 AS "test""number"</code> will display as a result a column with the name <code class="literal">test"number</code>.</p>
</div>
</div>
</div>

<div class="section">
<div class="titlepage"><div><div>
<h3 class="title">
<a id="sql-syntax-special-chars"></a>Special characters<a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/language/syntax/lexic/index.asciidoc">edit</a>
</h3>
</div></div></div>
<p>A few characters that are not alphanumeric have a dedicated meaning different from that of an operator. For completeness these are specified below:</p>
<div class="informaltable">
<table border="1" cellpadding="4px">
<colgroup>
<col class="col_1">
<col class="col_2">
</colgroup>
<tbody>
<tr>
<td align="center" valign="top"><p><span class="strong strong"><strong>Char</strong></span></p></td>
<td align="center" valign="top"><p><span class="strong strong"><strong>Description</strong></span></p></td>
</tr>
<tr>
<td align="center" valign="top"><p><code class="literal">*</code></p></td>
<td align="center" valign="top"><p>The asterisk (or wildcard) is used in some contexts to denote all fields for a table. Can be also used as an argument to some aggregate functions.</p></td>
</tr>
<tr>
<td align="center" valign="top"><p><code class="literal">,</code></p></td>
<td align="center" valign="top"><p>Commas are used to enumerate the elements of a list.</p></td>
</tr>
<tr>
<td align="center" valign="top"><p><code class="literal">.</code></p></td>
<td align="center" valign="top"><p>Used in numeric constants or to separate identifiers qualifiers (catalog, table, column names, etc…​).</p></td>
</tr>
<tr>
<td align="center" valign="top"><p><code class="literal">()</code></p></td>
<td align="center" valign="top"><p>Parentheses are used for specific SQL commands, function declarations or to enforce precedence.</p></td>
</tr>
</tbody>
</table>
</div>
</div>

<div class="section">
<div class="titlepage"><div><div>
<h3 class="title">
<a id="sql-syntax-operators"></a>Operators<a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/language/syntax/lexic/index.asciidoc">edit</a>
</h3>
</div></div></div>
<p>Most operators in Elasticsearch SQL have the same precedence and are left-associative. As this is done at parsing time, parenthesis need to be used to enforce a different precedence.</p>
<p>The following table indicates the supported operators and their precedence (highest to lowest);</p>
<div class="informaltable">
<table border="1" cellpadding="4px">
<colgroup>
<col class="col_1">
<col class="col_2">
<col class="col_3">
</colgroup>
<tbody>
<tr>
<td align="center" valign="top"><p><span class="strong strong"><strong>Operator/Element</strong></span></p></td>
<td align="center" valign="top"><p><span class="strong strong"><strong>Associativity</strong></span></p></td>
<td align="center" valign="top"><p><span class="strong strong"><strong>Description</strong></span></p></td>
</tr>
<tr>
<td align="center" valign="top"><p><code class="literal">.</code></p></td>
<td align="center" valign="top"><p>left</p></td>
<td align="center" valign="top"><p>qualifier separator</p></td>
</tr>
<tr>
<td align="center" valign="top"><p><code class="literal">::</code></p></td>
<td align="center" valign="top"><p>left</p></td>
<td align="center" valign="top"><p>PostgreSQL-style type cast</p></td>
</tr>
<tr>
<td align="center" valign="top"><p><code class="literal">+ -</code></p></td>
<td align="center" valign="top"><p>right</p></td>
<td align="center" valign="top"><p>unary plus and minus (numeric literal sign)</p></td>
</tr>
<tr>
<td align="center" valign="top"><p><code class="literal">* / %</code></p></td>
<td align="center" valign="top"><p>left</p></td>
<td align="center" valign="top"><p>multiplication, division, modulo</p></td>
</tr>
<tr>
<td align="center" valign="top"><p><code class="literal">+ -</code></p></td>
<td align="center" valign="top"><p>left</p></td>
<td align="center" valign="top"><p>addition, subtraction</p></td>
</tr>
<tr>
<td align="center" valign="top"><p><code class="literal">BETWEEN IN LIKE</code></p></td>
<td align="center" valign="top"><p></p></td>
<td align="center" valign="top"><p>range containment, string matching</p></td>
</tr>
<tr>
<td align="center" valign="top"><p><code class="literal"> = =   !=</code></p></td>
<td align="center" valign="top"><p></p></td>
<td align="center" valign="top"><p>comparison</p></td>
</tr>
<tr>
<td align="center" valign="top"><p><code class="literal">NOT</code></p></td>
<td align="center" valign="top"><p>right</p></td>
<td align="center" valign="top"><p>logical negation</p></td>
</tr>
<tr>
<td align="center" valign="top"><p><code class="literal">AND</code></p></td>
<td align="center" valign="top"><p>left</p></td>
<td align="center" valign="top"><p>logical conjunction</p></td>
</tr>
<tr>
<td align="center" valign="top"><p><code class="literal">OR</code></p></td>
<td align="center" valign="top"><p>left</p></td>
<td align="center" valign="top"><p>logical disjunction</p></td>
</tr>
</tbody>
</table>
</div>
</div>

<div class="section">
<div class="titlepage"><div><div>
<h3 class="title">
<a id="sql-syntax-comments"></a>Comments<a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/language/syntax/lexic/index.asciidoc">edit</a>
</h3>
</div></div></div>
<p>Elasticsearch SQL allows comments which are sequence of characters ignored by the parsers.</p>
<p>Two styles are supported:</p>
<div class="variablelist">
<dl class="variablelist">
<dt>
<span class="term">
Single Line
</span>
</dt>
<dd>
Comments start with a double dash <code class="literal">--</code> and continue until the end of the line.
</dd>
<dt>
<span class="term">
Multi line
</span>
</dt>
<dd>
Comments that start with <code class="literal">/*</code> and end with <code class="literal">*/</code> (also known as C-style).
</dd>
</dl>
</div>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">-- single line comment
/* multi
   line
   comment
   that supports /* nested comments */
   */</pre>
</div>
</div>

</div>
<div class="navfooter">
<span class="prev">
<a href="sql-spec.html">« SQL Language</a>
</span>
<span class="next">
<a href="sql-commands.html">SQL Commands »</a>
</span>
</div>
</div>

                  <!-- end body -->
                        </div>
                        <div class="col-xs-12 col-sm-4 col-md-4" id="right_col">
                        
                        </div>
                    </div>
                </div>
            </section>
        </div>
    </section>
</div>
<script src="../static/cn.js"></script>
</body>
</html>